import openpyxl

import os

current_path = os.getcwd()  # 获取当前工作目录
parent_path = os.path.dirname(current_path)  # 获取上级目录

# 打开Excel文件
excel_name = parent_path + '/原始数据/当天生日的车主_20231215134808.xlsx'
workbook = openpyxl.load_workbook(excel_name)

# 获取工作表
sheet = workbook.active

# 获取推送模版文件1(当天生日的车主-关注服务号-不含称谓.xlsm)
push_excel_name = '当天生日的车主-不关注服务号-不含称谓.xlsx'
push_workbook = openpyxl.load_workbook(push_excel_name)
push_sheet = push_workbook.active

# 获取推送模版文件2(当天生日的车主-关注服务号-常规.xlsm)
basic_excel_name = '当天生日的车主-不关注服务号-常规1.xlsx'
basic_workbook = openpyxl.load_workbook(basic_excel_name)
basic_sheet = basic_workbook.active

# 遍历指定列的所有单元格，获取Open ID数据（使用新的变量名）
for row in sheet.iter_rows(min_row=2):   # 从第二行开始，因为第一行可能是标题行
    # 获取除了关注之外的数据 并且手机号不能为空
    if row[3].value != '关注' and row[2].value != '':
        # 过滤手机号不包含有中文逗号的数据
        if '，' not in row[2].value:
            # 过滤姓名包含中文逗号或保时捷的数据
            if '，' in row[4].value or '保时捷' in row[4].value:
                # 输出该行的整行数据
                openid = [cell.value for cell in row][2]
                push_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 过滤掉称谓不含女士和先生的数据
            elif row[5].value != '女士' and row[5].value != '先生':
                openid = [cell.value for cell in row][2]
                push_sheet.append([openid, '保时捷车主'])
                # print(openid)
            # 常规数据
            else:
                openid_2 = [cell.value for cell in row][2]
                openid_4 = [cell.value for cell in row][4]
                openid_5 = [cell.value for cell in row][5]
                basic_sheet.append([openid_2, openid_4, openid_5])
                # print(openid_2, openid_4, openid_5)
# 保存推送的Excel文件
push_workbook.save(push_excel_name)
basic_workbook.save(basic_excel_name)